﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Com.JunCaiSoft.AddressManage.Entity;
using Com.JunCaiSoft.API.Common.Dto;
using Com.JunCaiSoft.AutoMapper;
using Com.JunCaiSoft.Common.AssistClass;
using Com.JunCaiSoft.Common.Controller;
using Com.JunCaiSoft.Common.Enums;
using Com.JunCaiSoft.Common.Model;
using Com.JunCaiSoft.ConstructionCashFlow.Entity;
using Com.JunCaiSoft.ConstructionCashFlow.IDao;
using Com.JunCaiSoft.ConstructionCashFlow.Model;
using Com.JunCaiSoft.Hr.Entity;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Newtonsoft.Json;

namespace Com.JunCaiSoft.API.ConstructionCashFlow.Controllers
{
    /// <summary>
    /// 银行账户管理
    /// </summary>
    [Route("api/[controller]/[action]")]
    [ApiController]
    [Authorize]
    public class BankController : BaseController<BankView,BankItem,Bank,Guid>
    {
        public BankController(IBankDao bankdao) : base(bankdao)
        {

        }
        /// <summary>
        /// 按银行ID单个删除
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        [HttpDelete]
        public override async Task<ActionResult> DeleteSingleAsync(Guid Id)
        {
            try
            {
                await DaoAsync.dbContext.Database.BeginTransactionAsync();
                int result = await DaoAsync.DeleteListAsync<Bank>(x => x.Id == Id);
                await DaoAsync.DeleteListAsync<BankSettlement>(x => x.SettlementBankId == Id);
                await DaoAsync.DeleteListAsync<CashFlow>(x => x.BankId == Id);

                return await Task.Run(() => Content(JsonConvert.SerializeObject(result)));
            }
            catch (Exception e)
            {
                await DaoAsync.dbContext.Database.RollbackTransactionAsync();

                throw e;
            }
            finally
            {
                await DaoAsync.dbContext.Database.CommitTransactionAsync();
            }
        }
        /// <summary>
        /// 按银行ID批量删除
        /// </summary>
        /// <param name="DeleteList"></param>
        /// <returns></returns>
        [HttpPatch]
        public virtual async Task<ActionResult> DeleteList(IEnumerable<Guid> DeleteList)
        {
            try
            {
                await DaoAsync.dbContext.Database.BeginTransactionAsync();
                int result = await DaoAsync.DeleteListAsync<Bank>(x => DeleteList.Contains(x.Id));
                await DaoAsync.DeleteListAsync<BankSettlement>(x => DeleteList.Contains(x.SettlementBankId));
                await DaoAsync.DeleteListAsync<CashFlow>(x => DeleteList.Contains(x.BankId));

                return await Task.Run(() => Content(JsonConvert.SerializeObject(result)));
            }
            catch (Exception e)
            {
                await DaoAsync.dbContext.Database.RollbackTransactionAsync();

                throw e;
            }
            finally
            {
                await DaoAsync.dbContext.Database.CommitTransactionAsync();
            }
        }
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize"></param>
        /// <param name="keyword">关键字</param>
        /// <returns></returns>
        [HttpGet]
        public virtual async Task<ActionResult> ListByPageAsync(int pageIndex, int pageSize,String keyword)
        {
            try
            {
                var columns = @" a.Id,
	                             a.Num,
	                             a.NumCode,
	                             c.Name Province,
	                             d.Name City,
	                             e.Name ZONE,
	                             f.Name Company,
	                             a.Name,
	                             a.Account,
	                             SUBSTRING ( a.Account, LEN( a.Account ) - 3, 4 ) AccountTail,
	                             ROUND(b.BeginBalance,2) BeginBalance,
	                             ROUND(b.EndBalance,2) EndBalance,
	                             ROUND(b.BeginFreezeMoney,2) FreezeMoney,
	                             ROUND(b.BeginFlowBalance,2) FlowBalance,
	                             g.Name AccountType,
	                             a.IsUsed,
	                             a.CreateTime  ";

                var tables = @" Bank a
	                            LEFT JOIN Administrative c ON a.Province = c.Code
	                            LEFT JOIN Administrative d ON a.City = d.Code
	                            LEFT JOIN Administrative e ON a.Zone = e.Code
	                            LEFT JOIN Company f ON a.Company = f.Id 
                                LEFT JOIN BankAccountType g ON a.AccountType = g.Code,
	                            (
	                                SELECT
		                                * 
	                                FROM
		                                ( SELECT *, ROW_NUMBER () OVER ( PARTITION BY SettlementBankId ORDER BY CreateTime ASC ) rn FROM BankSettlement a WHERE a.SettlementPeriod = 0 ) t 
	                                WHERE
		                                rn = 1 
	                            ) b ";

                var where = @" a.Id = b.SettlementBankId ";

                List<SqlParameter> bankParams = new List<SqlParameter>() { };

                if(keyword != "" && keyword != null)
                {

                    Regex rex = new Regex(@"^\d{4}$");

                    if (keyword.Length == 4 && rex.IsMatch(keyword))
                    {
                        where += " AND a.Account like @BankNameOrBankTail ";

                        SqlParameter QueryBankName = new SqlParameter("@BankNameOrBankTail", "%" + keyword );

                        bankParams.Add(QueryBankName);
                    }
                    else
                    {
                        where += " AND a.Name like @BankNameOrBankTail ";

                        SqlParameter QueryBankName = new SqlParameter("@BankNameOrBankTail", "%" + keyword + "%");

                        bankParams.Add(QueryBankName);
                    }

                }

                

                var resultList = await DaoAsync.QuerySQLByPageQuickAsync<BankItem>(pageIndex, pageSize, " a.CreateTime desc ", columns, tables, where, bankParams.Select(x => ((ICloneable)x).Clone()).ToArray());
                
                return await Task.Run(() => Content(JsonConvert.SerializeObject(resultList)));
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        [HttpPost]
        public override async Task<ActionResult> CreateAsync(BankView entity)
        {
            try
            {
                await DaoAsync.dbContext.Database.BeginTransactionAsync();

                var saveone = entity.MapTo<Bank>();
                var result = await DaoAsync.InsertAsync(saveone);
                var settlement = entity.MapTo<BankSettlement>();
                settlement.SettlementBankId = saveone.Id;
                await DaoAsync.InsertAsync(settlement);

                await DaoAsync.dbContext.Database.CommitTransactionAsync();
                return await Task.Run(() => Content(JsonConvert.SerializeObject(result)));
            }
            catch (Exception e) 
            {
                await DaoAsync.dbContext.Database.RollbackTransactionAsync();

                throw e;
            }
            
        }
        [HttpPut]
        public override async Task<ActionResult> UpdateAsync(UpdateEntity<BankView> entityModel)
        {
            try
            {
                await DaoAsync.dbContext.Database.BeginTransactionAsync();

                var saveone = entityModel.entity.MapTo<Bank>();
                var result = await DaoAsync.UpdateAsync(saveone, entityModel.Id);
                var settlement = entityModel.entity.MapTo<BankSettlement>();
                settlement.SettlementBankId = saveone.Id;
                await DaoAsync.UpdateAsync(settlement, settlement.Id);

                await DaoAsync.dbContext.Database.CommitTransactionAsync();
                return await Task.Run(() => Content(JsonConvert.SerializeObject(result)));
            }
            catch(Exception e)
            {
                await DaoAsync.dbContext.Database.RollbackTransactionAsync();

                throw e;
            }
        }
        [HttpGet]
        public override async Task<ActionResult> GetAsync(Guid Id)
        {
            var columns = @"a.Id,
	                        a.Num,
	                        a.NumCode,
	                        a.Province,
	                        a.City,
	                        a.Zone,
	                        a.Company,
	                        a.Name,
	                        a.Account,
                            SUBSTRING(a.Account, LEN(a.Account) - 3, 4) AccountTail,
                            b.Id SettlementBankId,
                            b.BeginTime,
                            b.EndTime,
                            b.SettlementPeriod,
	                        ROUND(b.BeginBalance,2) BeginBalance,
	                        ROUND(b.EndBalance,2) EndBalance,
                            ROUND(b.BeginCommercialTicket,2) BeginCommercialTicket,
	                        ROUND(b.EndCommercialTicket,2) EndCommercialTicket,
	                        ROUND(b.BeginFreezeMoney,2) FreezeMoney,
	                        ROUND(b.BeginFlowBalance,2) FlowBalance,
                            b.IsSettlement,
                            a.AccountType,
	                        a.IsUsed,
	                        a.CreateTime ";

            var tables = @" Bank a,
	                        (
	                        SELECT
		                        * 
	                        FROM
		                        ( SELECT *, ROW_NUMBER () OVER ( PARTITION BY SettlementBankId ORDER BY CreateTime ASC ) rn FROM BankSettlement a  WHERE a.SettlementPeriod = 0 ) t 
	                        WHERE
		                        rn = 1 
	                        ) b ";

            var where = @" a.Id = b.SettlementBankId and a.Id = @Id ";

            SqlParameter parameter = new SqlParameter("@Id", Id);

            List<SqlParameter> arrary = new List<SqlParameter>();

            arrary.Add(parameter);

            var resultList = await DaoAsync.QuerySQLByPageQuickAsync<BankView>(1,10," a.CreateTime desc ", columns, tables, where, arrary.Select(x => ((ICloneable)x).Clone()).ToArray());

           

            var resultOne = resultList.items != null ? resultList.items.FirstOrDefault() : new BankView() ;

            return await Task.Run(() => Content(JsonConvert.SerializeObject(resultOne)));
        }
        /// <summary>
        /// 查询银行
        /// </summary>
        /// <param name="QueryBank"></param>
        /// <returns></returns>
        [HttpGet]
        public virtual async Task<ActionResult> QueryBank(String QueryBank) 
        {
            try
            {
                int result = 0;

                return await Task.Run(() => Content(JsonConvert.SerializeObject(result)));
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        /// <summary>
        /// 小写转大写金额
        /// </summary>
        /// <param name="smallmoney"></param>
        /// <returns></returns>
        [HttpGet]
        public virtual async Task<ActionResult> ToBigMoney(Double smallmoney) 
        {
            try
            {
                String result = BigMoney.GetCnString(smallmoney.ToString());

                if(!smallmoney.ToString().Contains('.') || smallmoney.ToString().Split('.')[1].Length < 2)
                {
                    result += "整";
                }

                return await Task.Run(() => Content(JsonConvert.SerializeObject(result)));
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        /// <summary>
        /// 按银行名称或银行尾号查询银行信息
        /// </summary>
        /// <param name="BankNameOrBankTail"></param>
        /// <returns></returns>
        [HttpGet]
        public virtual async Task<ActionResult> QueryCashFlowBank(String BankNameOrBankTail)
        {
            try
            {
                var sql = @"select * from Bank a ";
                List<SqlParameter> arrary = new List<SqlParameter>();

                if(BankNameOrBankTail != null)
                {
                    sql += "where a.Name like @BankName ";

                    SqlParameter para1 = new SqlParameter("@BankName", SqlDbType.VarChar);
                    para1.Value = BankNameOrBankTail != null ? "%" + BankNameOrBankTail + "%" : "''";

                    arrary.Add(para1);

                    sql += " union ";

                    sql += @" select * from Bank a 
                                where a.Account like @BankTail";

                    SqlParameter para2 = new SqlParameter("@BankTail", SqlDbType.VarChar);
                    para2.Value = BankNameOrBankTail != null ? "%" + BankNameOrBankTail : "''";

                    arrary.Add(para2);
                }

                sql += " Order by a.CreateTime desc";

                var list = await DaoAsync.QuerySQLAsync<Bank>(sql, arrary.Select(x => ((ICloneable)x).Clone()).ToArray());

                var companyList = list.ToList().Select(x=> x.Company).Distinct();

                List<CashFlowBank> resultList = new List<CashFlowBank>();

                int i = 0;

                foreach(var item in companyList)
                {
                    CashFlowBank one = new CashFlowBank();
                    one.OrderNum = i;
                    var company = DaoAsync.GetAsync<Company>(x => x.Id == item).Result;
                    one.Company = company != null ? company.Name : "";
                    one.Children = list.ToList().Where(x => x.Company == item).ToList();
                    resultList.Add(one);
                    i++;
                }
                return await Task.Run(() => Content(JsonConvert.SerializeObject(resultList)));
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }
}
